/*
{
"状态":1,
"名称":"导入-款图片",
"作者":"马学兵",
"创建时间":"2018-11-15",
"功能":"导入-款图片",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/
SET XACT_ABORT ON --设置事务回滚开关

--前端调用
DECLARE @json nvarchar(max)

--定义变量
DECLARE @user_key INT 


SET @user_key='1' 
 
set @json = '{jsonobj}'  
 
 /*
set @json='{
        "docdate": ""
        "subs": [
            {"goods_no": "K123","colorid": "x","pic":"1"}
        ]
    }'
 
*/

if object_id('tempdb..#dt')<>0 
	drop table #dt
	 
select rowid , 
max(case when fieldname = 'goods_no' then stringValue else '' end) as goods_no,
max(case when fieldname = 'colorid' then stringValue else '' end) as colorid, 
max(case when fieldname = 'pic' then stringValue else '' end) as pic 
INTO #dt
from dbo.yiparse(@json,'subs') 
group by rowid


if exists(select 1 from #dt where goods_no='')
begin 
	RAISERROR ('款 存在为空的行,请检查', 16 ,1 ) ; 
	return 
end 

if exists(select 1 from #dt where colorid='')
begin 
	RAISERROR ('颜色 存在为空的行,请检查', 16 ,1 ) ; 
	return 
end 


if exists(select 1 from #dt where pic='')
begin 
	RAISERROR ('图片地址 存在为空的行,请检查', 16 ,1 ) ; 
	return 
end 
 

--插入数据
begin tran 
	INSERT INTO dbo.r_style_pic( [styleno], [colorid], [url], [create_user], [create_time] )
	SELECT goods_no, a.colorid,pic,@user_key,getdate() FROM #dt A left join [r_style_pic] B on
	a.goods_no=B.styleno and a.colorid=b.colorid
	where b.styleno is null 

	update a set url=b.pic
	from r_style_pic a inner join  #dt b on a.styleno=b.goods_no and a.colorid=b.colorid


 IF @@ERROR<>0
	  ROLLBACK
	ELSE 
	 COMMIT
	 
DROP TABLE #dt

 